﻿using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Configuration;
using NewLife;
using NewLife.Log;
using SqlSugar;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Yitter.IdGenerator;
using YY.Admin.Core.Const;
using YY.Admin.Core.Extension;
using YY.Admin.Core.Option;
using DbType = SqlSugar.DbType;

namespace YY.Admin.Core.SqlSugar
{
    public class SqlSugarDbContext : ISqlSugarDbContext
    {
        public SqlSugarScope Db { get; }
        // 是否正在处理种子数据
        private static bool _isHandlingSeedData = false;
        private readonly DbConnectionOptions _dbConnectionOptions;

        public SqlSugarDbContext(DbConnectionOptions dbConnectionOptions)
        {
            _dbConnectionOptions= dbConnectionOptions;
            // 注册雪花Id
            var snowIdOpt = new IdGeneratorOptions
            {
                // 扩展 WorkerId 位数至 12 位 (0-4095)
                WorkerIdBitLength = 12,

                // 减少序列号位数保持 64 位总长
                SeqBitLength = 8,

                // 调整时间戳位数 (仍可支持 68 年)
                BaseTime = DateTime.Now.AddYears(-30)
            };
            YitIdHelper.SetIdGenerator(snowIdOpt);

            // 自定义 SqlSugar 雪花ID算法
            SnowFlakeSingle.WorkId = snowIdOpt.WorkerId;
            StaticConfig.CustomSnowFlakeFunc = () =>
            {
                return YitIdHelper.NextId();
            };
            // 动态表达式 SqlFunc 支持，https://www.donet5.com/Home/Doc?typeId=2569
            StaticConfig.DynamicExpressionParserType = typeof(DynamicExpressionParser);
            StaticConfig.DynamicExpressionParsingConfig = new ParsingConfig
            {
                CustomTypeProvider = new SqlSugarTypeProvider()
            };
           Db = new SqlSugarScope(SetDbConfig(_dbConnectionOptions.ConnectionConfigs));
           SetDbAop(Db, _dbConnectionOptions.EnableConsoleSql, _dbConnectionOptions.SuperAdminIgnoreIDeletedFilter);
           InitDatabase(Db, _dbConnectionOptions.ConnectionConfigs);
        }
        /// <summary>
        /// 配置Aop
        /// </summary>
        /// <param name="db"></param>
        /// <param name="enableConsoleSql"></param>
        /// <param name="superAdminIgnoreIDeletedFilter"></param>
        public static void SetDbAop( SqlSugarScope db, bool enableConsoleSql, bool superAdminIgnoreIDeletedFilter)
        {
            // 设置超时时间
            db.Ado.CommandTimeOut = 30;

            // 打印SQL语句
            if (enableConsoleSql)
            {
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    var log = $"【{DateTime.Now}——执行SQL】\r\n{UtilMethods.GetNativeSql(sql, pars)}\r\n";
                    var originColor = Console.ForegroundColor;
                    if (sql.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
                        Console.ForegroundColor = ConsoleColor.Green;
                    if (sql.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) || sql.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase))
                        Console.ForegroundColor = ConsoleColor.Yellow;
                    if (sql.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase))
                        Console.ForegroundColor = ConsoleColor.Red;
                    Debug.WriteLine(log);
                    Console.ForegroundColor = originColor;
                };
            }
            db.Aop.OnError = ex =>
            {
                if (ex.Parametres == null) return;
                var log = $"【{DateTime.Now}——错误SQL】\r\n{UtilMethods.GetNativeSql(ex.Sql, (SugarParameter[])ex.Parametres)}\r\n";
                Debug.WriteLine(log);
                // Log.Error(log, ex);
            };
            db.Aop.OnLogExecuted = (sql, pars) =>
            {
                // 执行时间超过5秒时
                if (!(db.Ado.SqlExecutionTime.TotalSeconds > 5)) return;

                var fileName = db.Ado.SqlStackTrace.FirstFileName; // 文件名
                var fileLine = db.Ado.SqlStackTrace.FirstLine; // 行号
                var firstMethodName = db.Ado.SqlStackTrace.FirstMethodName; // 方法名
                var log = $"【{DateTime.Now}——超时SQL】\r\n【所在文件名】：{fileName}\r\n【代码行数】：{fileLine}\r\n【方法名】：{firstMethodName}\r\n" + $"【SQL语句】：{UtilMethods.GetNativeSql(sql, pars)}";
                Debug.WriteLine(log);
                //  Log.Warning(log);
            };
            
        }
        /// <summary>
        /// 配置连接属性
        /// </summary>
        /// <param name="config"></param>
        /// <returns></returns>
        public static DbConnectionConfig SetDbConfig(DbConnectionConfig config)
        {

            if (config.DbSettings.EnableConnStringEncrypt)
                config.ConnectionString = CryptogramUtil.Decrypt(config.ConnectionString);

            var configureExternalServices = new ConfigureExternalServices
            {
                EntityNameService = (type, entity) => // 处理表
                {
                    entity.IsDisabledDelete = true; // 禁止删除非 sqlsugar 创建的列
                                                    // 只处理贴了特性[SugarTable]表
                    if (!type.GetCustomAttributes<SugarTable>().Any())
                        return;
                    if (config.DbSettings.EnableUnderLine && !entity.DbTableName.Contains('_'))
                        entity.DbTableName = UtilMethods.ToUnderLine(entity.DbTableName); // 驼峰转下划线
                },
                EntityService = (type, column) => // 处理列
                {
                    // 只处理贴了特性[SugarColumn]列
                    if (!type.GetCustomAttributes<SugarColumn>().Any())
                        return;
                    if (new NullabilityInfoContext().Create(type).WriteState is NullabilityState.Nullable)
                        column.IsNullable = true;
                    if (config.DbSettings.EnableUnderLine && !column.IsIgnore && !column.DbColumnName.Contains('_'))
                        column.DbColumnName = UtilMethods.ToUnderLine(column.DbColumnName); // 驼峰转下划线
                },
            };
            config.ConfigureExternalServices = configureExternalServices;
            config.InitKeyType = InitKeyType.Attribute;
            config.IsAutoCloseConnection = true;
            config.MoreSettings = new ConnMoreSettings
            {
                IsAutoRemoveDataCache = true, // 启用自动删除缓存，所有增删改会自动调用.RemoveDataCache()
                IsAutoDeleteQueryFilter = true, // 启用删除查询过滤器
                IsAutoUpdateQueryFilter = true, // 启用更新查询过滤器
                SqlServerCodeFirstNvarchar = true // 采用Nvarchar
            };

            // 若库类型是人大金仓则默认设置PG模式
            if (config.DbType == DbType.Kdbndp)
                config.MoreSettings.DatabaseModel = DbType.PostgreSQL; // 配置PG模式主要是兼容系统表差异

            // 若库类型是Oracle则默认主键名字和参数名字最大长度
            if (config.DbType == DbType.Oracle)
                config.MoreSettings.MaxParameterNameLength = 30;
            return config;
        }
        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <param name="db">SqlSugarScope 实例</param>
        /// <param name="config">数据库连接配置</param>
        private static void InitDatabase(SqlSugarScope db, DbConnectionConfig config)
        {
            var dbProvider = db.GetConnectionScope(config.ConfigId);

            // 等待数据库连接就绪
            WaitForDatabaseReady(dbProvider);

            // 初始化数据库
            if (config.DbSettings.EnableInitDb)
            {
                //Log.Information($"初始化数据库 {config.DbType} - {config.ConfigId} - {config.ConnectionString}");
                if (config.DbType != DbType.Oracle) dbProvider.DbMaintenance.CreateDatabase();
            }

            // 初始化表结构
            if (config.TableSettings.EnableInitTable)
            {
                //Log.Information($"初始化表结构 {config.DbType} - {config.ConfigId}");
                var entityTypes = GetEntityTypesForInit(config);
                InitializeTables(dbProvider, entityTypes, config);
            }
            //// 初始化视图
            //if (config.DbSettings.EnableInitView) InitView(dbProvider);
            // 初始化种子数据
            if (config.SeedSettings.EnableInitSeed) InitSeedData(db, config);
        }
        /// <summary>
        /// 初始化种子数据
        /// </summary>
        /// <param name="db">SqlSugarScope 实例</param>
        /// <param name="config">数据库连接配置</param>
        private static void InitSeedData(SqlSugarScope db, DbConnectionConfig config)
        {
            var dbProvider = db.GetConnectionScope(config.ConfigId);
            _isHandlingSeedData = true;

           // Log.Information($"初始化种子数据 {config.DbType} - {config.ConfigId}");
            var seedDataTypes = GetSeedDataTypes(config);

            int count = 0, sum = seedDataTypes.Count;
            var tasks = seedDataTypes.Select(seedType => Task.Run(() =>
            {
                var entityType = seedType.GetInterfaces().First().GetGenericArguments().First();
                if (!IsEntityForConfig(entityType, config)) return;

                var seedData = GetSeedData(seedType);
                if (seedData == null) return;

                AdjustSeedDataIds(seedData, config);
                InsertOrUpdateSeedData(dbProvider, seedType, entityType, seedData, config, ref count, sum);
            }));

            Task.WhenAll(tasks).GetAwaiter().GetResult();
            _isHandlingSeedData = false;
        }
        /// <summary>
        /// 获取需要初始化的实体类型
        /// </summary>
        /// <param name="config">数据库连接配置</param>
        /// <returns>实体类型列表</returns>
        /// <summary>
        /// 获取需要初始化的实体类型
        /// </summary>
        /// <param name="config">数据库连接配置</param>
        /// <returns>实体类型列表</returns>
        private static List<Type> GetEntityTypesForInit(DbConnectionConfig config)
        {
            // 获取当前应用程序域中的所有程序集
            var assemblies = AppDomain.CurrentDomain.GetAssemblies();

            // 收集所有符合条件的类型
            var allTypes = new List<Type>();
            foreach (var assembly in assemblies)
            {
                try
                {
                    // 跳过动态程序集（通常不包含用户定义的实体类）
                    if (assembly.IsDynamic) continue;

                    // 获取程序集中定义的所有类型
                    var types = assembly.GetTypes();
                    allTypes.AddRange(types);
                }
                catch (ReflectionTypeLoadException ex)
                {
                    // 处理部分类型加载失败的情况
                    var loadedTypes = ex.Types.Where(t => t != null);
                    allTypes.AddRange(loadedTypes);
                }
                catch
                {
                    // 忽略无法加载的程序集
                }
            }

            // 过滤类型
            return allTypes
                .Where(u => u != null)
                .Where(u => u.IsClass && !u.IsAbstract && !u.IsInterface)
                .Where(u => u.IsDefined(typeof(SugarTable), false))
                .Where(u => !u.IsDefined(typeof(IgnoreTableAttribute), false))
                .WhereIF(config.TableSettings.EnableIncreTable, u => u.IsDefined(typeof(IncreTableAttribute), false))
                .Where(u => IsEntityForConfig(u, config))
                .ToList();
        }
        /// <summary>
        /// 获取种子数据类型
        /// </summary>
        /// <param name="config">数据库连接配置</param>
        /// <returns>种子数据类型列表</returns>
        private static List<Type> GetSeedDataTypes(DbConnectionConfig config)
        {
            // 获取所有程序集及其类型
            var allTypes = AppDomain.CurrentDomain.GetAssemblies()
                .Where(a => !a.IsDynamic) // 跳过动态程序集
                .SelectMany(assembly =>
                {
                    try
                    {
                        return assembly.GetTypes();
                    }
                    catch (ReflectionTypeLoadException ex)
                    {
                        // 处理部分类型加载失败的情况
                        return ex.Types?.Where(t => t != null) ?? Enumerable.Empty<Type>();
                    }
                    catch
                    {
                        // 忽略无法加载的程序集
                        return Enumerable.Empty<Type>();
                    }
                })
                .Where(t => t != null)
                .ToList();

            // 过滤种子数据类型
            var seedTypes = allTypes
                .Where(u => u.IsClass && !u.IsAbstract && !u.IsInterface)
                .Where(u => u.HasImplementedRawGeneric(typeof(ISqlSugarEntitySeedData<>)))
                .WhereIF(config.SeedSettings.EnableIncreSeed,
                        u => u.IsDefined(typeof(IncreSeedAttribute), false))
                .OrderBy(u =>
                {
                    var seedAttrs = u.GetCustomAttributes(typeof(SeedDataAttribute), false);
                    return seedAttrs.Length > 0 ? ((SeedDataAttribute)seedAttrs[0]).Order : 0;
                })
                .ToList();

            return seedTypes;
        }
        /// <summary>
        /// 判断实体是否属于当前配置
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="config">数据库连接配置</param>
        /// <returns>是否属于当前配置</returns>
        private static bool IsEntityForConfig(Type entityType, DbConnectionConfig config)
        {
            switch (config.ConfigId.ToString())
            {
                case SqlSugarConst.MainConfigId:
                    return entityType.GetCustomAttributes<SysTableAttribute>().Any() ||
                           (!entityType.GetCustomAttributes<LogTableAttribute>().Any() &&
                            !entityType.GetCustomAttributes<TenantAttribute>().Any());

                case SqlSugarConst.LogConfigId:
                    return entityType.GetCustomAttributes<LogTableAttribute>().Any();

                default:
                    {
                        var tenantAttribute = entityType.GetCustomAttribute<TenantAttribute>();
                        return tenantAttribute != null && tenantAttribute.configId.ToString() == config.ConfigId.ToString();
                    }
            }
        }
        /// <summary>
        /// 初始化表结构
        /// </summary>
        /// <param name="dbProvider">SqlSugarScopeProvider 实例</param>
        /// <param name="entityTypes">实体类型列表</param>
        /// <param name="config">数据库连接配置</param>
        private static void InitializeTables(SqlSugarScopeProvider dbProvider, List<Type> entityTypes, DbConnectionConfig config)
        {
            int count = 0, sum = entityTypes.Count;
            var tasks = entityTypes.Select(entityType => Task.Run(() =>
            {
                Console.WriteLine($"初始化表结构 {entityType.FullName,-64} ({config.ConfigId} - {Interlocked.Increment(ref count):D003}/{sum:D003})");
                UpdateNullableColumns(dbProvider, entityType);
                InitializeTable(dbProvider, entityType);
            }));

            Task.WhenAll(tasks).GetAwaiter().GetResult();
        }
        /// <summary>
        /// 更新表中不存在于实体的字段为可空
        /// </summary>
        /// <param name="dbProvider">SqlSugarScopeProvider 实例</param>
        /// <param name="entityType">实体类型</param>
        private static void UpdateNullableColumns(SqlSugarScopeProvider dbProvider, Type entityType)
        {
            var entityInfo = dbProvider.EntityMaintenance.GetEntityInfo(entityType);
            var dbColumns = dbProvider.DbMaintenance.GetColumnInfosByTableName(entityInfo.DbTableName) ?? new List<DbColumnInfo>();

            foreach (var dbColumn in dbColumns.Where(c => !c.IsPrimarykey && entityInfo.Columns.All(u => u.DbColumnName != c.DbColumnName)))
            {
                dbColumn.IsNullable = true;
                Retry(() =>
                {
                    dbProvider.DbMaintenance.UpdateColumn(entityInfo.DbTableName, dbColumn);
                }, maxRetry: 3, retryIntervalMs: 1000);
            }
        }
        /// <summary>
        /// 等待数据库就绪
        /// </summary>
        /// <param name="dbProvider"></param>
        private static void WaitForDatabaseReady(SqlSugarScopeProvider db)
        {
            do
            {
                try
                {
                    if (db.Ado.Connection.State != ConnectionState.Open)
                        db.Ado.Connection.Open();

                    // 如果连接成功，直接返回
                   // Log.Information("数据库连接成功。");
                    return;
                }
                catch (Exception ex)
                {
                   // Log.Warning($"数据库尚未就绪，等待中... 错误：{ex.Message}");
                    Thread.Sleep(1000);
                }
            } while (true);
        }
        /// <summary>
        /// 简单的重试机制
        /// </summary>
        /// <param name="action"></param>
        /// <param name="maxRetry"></param>
        /// <param name="retryIntervalMs"></param>
        private static void Retry(Action action, int maxRetry, int retryIntervalMs)
        {
            int attempt = 0;
            while (true)
            {
                try
                {
                    action();
                    return;
                }
                catch (SqliteException ex) when (ex.SqliteErrorCode == 5) // SQLITE_BUSY
                {
                    if (++attempt >= maxRetry)
                    {
                        //Log.Error($"简单的重试机制:{ex.Message}"); throw;
                    }
                    //Log.Information($"数据库忙，正在重试... (尝试 {attempt}/{maxRetry})");
                    Thread.Sleep(retryIntervalMs);
                }
            }
        }
        /// <summary>
        /// 获取种子数据
        /// </summary>
        /// <param name="seedType">种子数据类型</param>
        /// <returns>种子数据列表</returns>
        private static IEnumerable<object> GetSeedData(Type seedType)
        {
            var instance = Activator.CreateInstance(seedType);
            var hasDataMethod = seedType.GetMethod("HasData");
            return ((IEnumerable)hasDataMethod?.Invoke(instance, null))?.Cast<object>();
        }
        /// <summary>
        /// 插入或更新种子数据
        /// </summary>
        /// <param name="dbProvider">SqlSugarScopeProvider 实例</param>
        /// <param name="seedType">种子数据类型</param>
        /// <param name="entityType">实体类型</param>
        /// <param name="seedData">种子数据列表</param>
        /// <param name="config">数据库连接配置</param>
        /// <param name="count">当前处理的数量</param>
        /// <param name="sum">总数量</param>
        private static void InsertOrUpdateSeedData(SqlSugarScopeProvider dbProvider, Type seedType, Type entityType, IEnumerable<object> seedData, DbConnectionConfig config, ref int count, int sum)
        {
            var entityInfo = dbProvider.EntityMaintenance.GetEntityInfo(entityType);
            var dataList = seedData.ToList();

            if (entityType.GetCustomAttribute<SplitTableAttribute>(true) != null)
            {
                var initMethod = seedType.GetMethod("Init");
                initMethod?.Invoke(Activator.CreateInstance(seedType), new object[] { dbProvider });
            }
            else
            {
                int updateCount = 0, insertCount = 0;
                if (entityInfo.Columns.Any(u => u.IsPrimarykey))
                {
                    var storage = dbProvider.StorageableByObject(dataList).ToStorage();
                    if (seedType.GetCustomAttribute<IgnoreUpdateSeedAttribute>() == null)
                    {
                        updateCount = storage.AsUpdateable
                            .IgnoreColumns(entityInfo.Columns
                                .Where(u => u.PropertyInfo.GetCustomAttribute<IgnoreUpdateSeedColumnAttribute>() != null)
                                .Select(u => u.PropertyName).ToArray())
                            .ExecuteCommand();
                    }
                    insertCount = storage.AsInsertable.ExecuteCommand();
                }
                else
                {
                    if (!dbProvider.Queryable(entityInfo.DbTableName, entityInfo.DbTableName).Any())
                    {
                        insertCount = dataList.Count;
                        dbProvider.InsertableByObject(dataList).ExecuteCommand();
                    }
                }
                Console.WriteLine($"添加数据 {entityInfo.DbTableName,-32} ({config.ConfigId} - {Interlocked.Increment(ref count):D003}/{sum:D003}，数据量：{dataList.Count:D003}，插入 {insertCount:D003} 条记录，修改 {updateCount:D003} 条记录)");
            }
        }
        /// <summary>
        /// 调整种子数据的 ID
        /// </summary>
        /// <param name="seedData">种子数据列表</param>
        /// <param name="config">数据库连接配置</param>
        private static void AdjustSeedDataIds(IEnumerable<object> seedData, DbConnectionConfig config)
        {
            var seedId = config.ConfigId.ToLong();
            foreach (var data in seedData)
            {
                var idProperty = data.GetType().GetProperty(nameof(EntityBaseId.Id));
                if (idProperty == null || idProperty.PropertyType != typeof(Int64)) continue;

                var idValue = idProperty.GetValue(data);
                if (idValue == null || idValue.ToString() == "0" || string.IsNullOrWhiteSpace(idValue.ToString()))
                {
                    idProperty.SetValue(data, ++seedId);
                }
            }
        }
        /// <summary>
        /// 初始化表
        /// </summary>
        /// <param name="dbProvider">SqlSugarScopeProvider 实例</param>
        /// <param name="entityType">实体类型</param>
        private static void InitializeTable(SqlSugarScopeProvider dbProvider, Type entityType)
        {
            Retry(() =>
            {
                if (entityType.GetCustomAttribute<SplitTableAttribute>() == null)
                {
                    dbProvider.CodeFirst.InitTables(entityType);
                }
                else
                {
                    dbProvider.CodeFirst.SplitTables().InitTables(entityType);
                }
            }, maxRetry: 3, retryIntervalMs: 1000);
        }

    }
}
